Many database systems do not support sorting clauses in database view defining statements. Many database customers want the ability to create a complex view with implicit sorting for reporting requirements and to hide the complexities of report queries, including ordering schemes, from the report user. However, since most databases do not support sorting clauses in view defining statements, the customer cannot create these kinds of canned views.
One reason sorting clauses are not supported in view defining statement is that the materialization of a view results in the creation of a large temporary table, with the sorting performed on the rows in the large temporary table. The report query is then created from the sorted temporary table. Even when the query is to return a small number of rows or columns from the temporary table, the entire temporary table would still need to be sorted. The creation of the temporary table, and the sorting of the rows in it, requires significant resources that impacts performance. The performance impact is increased when the view defining statement and the query together have multiple sorts, involve a table join, or involve nested views or queries.